﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 系统设置选项表操作类(初步审核通过)
    /// </summary>
    public class OptionDAO : IOption
    {
        /// <summary>
        /// 向选项设置表中插入一行数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="option">要插入的选项对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(Option option)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Options]
                                    (
                                        [option_name],
                                        [option_value],
                                        [option_isenable],
                                        [option_desc]
                                    )
                                    VALUES
                                    (
                                        @Name,
                                        @Value,
                                        @IsEnable,
                                        @Description
                                    );";
            return ExecuteSql(option, sql);
        }

        /// <summary>
        /// 根据选项ID删除对应的一条选项记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除的选项ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Options] WHERE [option_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据选项ID数组删除对应的多条选项记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除的选项ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            //拼接要删除的ID
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Options] WHERE [option_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 根据选项名称更新对应的选项值, 返回执行结果受影响行数
        /// </summary>
        /// <param name="name">要修改的选项名称</param>
        /// <param name="value">要更新的值</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateValue(string name, string value)
        {
            string sql = "UPDATE [&#WednTablePrefix%&_Options] SET [option_value]=@Value WHERE [option_name]=@Name;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Value", value), new SqlParameter("@Name", name));
        }

        /// <summary>
        /// 更新一个完整选项对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="option">要更新的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateOption(Option option)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Options] 
                                   SET 
                                        [option_name]=@Name,
                                        [option_value]=@Value,
                                        [option_isenable]=@IsEnable,
                                        [option_desc]=@Description
                                    WHERE 
                                        [option_id]=@Id;";
            return ExecuteSql(option, sql);
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("option_id", "&#WednTablePrefix%&_Options");
        }

        /// <summary>
        /// 检查表中是否已经存在该选项名称, 返回一个布尔值, 存在则为True; 不存在则为False
        /// </summary>
        /// <param name="name">要确定的选项名称</param>
        /// <returns>存在返回True; 不存在返回False</returns>
        public bool ExistsKey(string name)
        {
            return SqlHelper.ColumnExistsValue("&#WednTablePrefix%&_Option", "option_name", name);
        }

        /// <summary>
        /// 取出选项表中所有选项设置信息, 返回选项对象集合, 如果不存在则返回null
        /// </summary>
        /// <returns>所有选项对象集合</returns>
        public List<Option> SelectOptionList()
        {
            string sql = @"SELECT 
                                        [option_id],
                                        [option_name], 
                                        [option_value],
                                        [option_isenable], 
                                        [option_desc]
                                    FROM 
                                        [&#WednTablePrefix%&_Options];";
            return Select(sql);
        }

        /// <summary>
        /// 根据选项名称选取选项表中对应的选项值, 返回对应选项值, 如果不存在则返回null
        /// </summary>
        /// <param name="name">选项名称</param>
        /// <returns>对应的选项值</returns>s
        public string SelectValue(string name)
        {
            string sql = "SELECT [option_value] FROM [&#WednTablePrefix%&_Options] WHERE [option_name]=@Name;";
            object res = SqlHelper.ExecuteScalar(sql, new SqlParameter("@Name", name));
            return res != null ? res.ToString() : null;
        }

        /// <summary>
        /// 根据选项名称选取选项表中对应的选项值, 返回对应选项值, 如果不存在则返回null
        /// </summary>
        /// <param name="names">选项名称数组</param>
        /// <returns>对应的选项值数组</returns>
        public string[] SelectValue(string[] names)
        {
            //拼接要查询的名称
            string ps = string.Join(",", names);
            string sql = "SELECT [option_value] FROM [&#WednTablePrefix%&_Options] WHERE [option_name] in (" + ps + ");";
            SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            string[] res = null;
            if (reader.HasRows)
            {
                res = new string[names.Length];
                int i = 0;
                while (reader.Read())
                {
                    res[i] = reader.GetString(0);
                    i++;
                }
            }
            return res;
        }

        /// <summary>
        /// 根据选项名称选取选项表中对应的选项值, 返回对应选项键值对, 如果不存在则返回null
        /// </summary>
        /// <param name="names">选项名称数组(选取全部则传入null)</param>
        /// <returns>对应的选项值数组</returns>
        public Dictionary<string, object> SelectKeyValueDict(string[] names)
        {
            string sql = "SELECT [option_name],[option_value] FROM [&#WednTablePrefix%&_Options]";
            if (names != null)
            {
                //拼接要查询的名称
                string ps = string.Join(",", names);
                sql += " WHERE [option_name] in (" + ps + ");";
            }
            Dictionary<string, object> dict = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql))
            {
                if (reader.HasRows)
                {
                    dict = new Dictionary<string, object>();
                    while (reader.Read())
                    {
                        //dict[reader.GetString(0).Replace("_", string.Empty)] = reader[1];
                        //不破坏原有数据
                        dict[reader.GetString(0)] = reader[1];
                    }
                }
            }
            return dict;
        }

        /// <summary>
        /// 根据选项ID选取对应的选项对象, 返回一个选项对象, 如果不存在则返回null
        /// </summary>
        /// <param name="id">选项ID</param>
        /// <returns>指定ID的选项对象</returns>s
        public Option SelectOption(int id)
        {
            string sql = @"SELECT 
                                        [option_id],
                                        [option_name], 
                                        [option_value],
                                        [option_isenable], 
                                        [option_desc]
                                    FROM 
                                        [&#WednTablePrefix%&_Options]
                                    WHERE
                                        [option_id]=@Id;";
            List<Option> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 私有执行方法
        /// </summary>
        /// <param name="option">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>执行结果受影响行数</returns>
        private static int ExecuteSql(Option option, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Name", option.Name.ToLower()),
                new SqlParameter("@Value", option.Value),
                new SqlParameter("@IsEnable", option.IsEnable),
                new SqlParameter("@Description", string.IsNullOrEmpty(option.Description) ? System.DBNull.Value : (object)option.Description),
                new SqlParameter("@Id", option.Id)
                );
        }
        /// <summary>
        /// 私有选择方法, 如果不存在则返回null
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>执行结果集合</returns>
        private static List<Option> Select(string sql, params SqlParameter[] parameter)
        {
            List<Option> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Option>();
                    while (reader.Read())
                    {
                        Option option = new Option()
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Value = reader.GetString(2),
                            IsEnable = reader.GetBoolean(3),
                            //reader.GetString(4));
                            //空值处理
                            Description = reader.IsDBNull(4) ? null : reader.GetString(4)
                        };
                        list.Add(option);
                    }
                }
            }
            return list;
        }
    }
}
